package cn.gxj62;

import java.sql.*;
import java.util.ArrayList;

public class mysql {
    public static Connection SQLConnect() {
        try {
            Connection con;
            Class.forName("com.mysql.jdbc.Driver");  //加载数据库驱动
            String url = "jdbc:mysql://localhost:3306/students?autoReconnect=true&useSSL=false";   //*****位置填写数据库的名字
            String usename = "root";  //数据库用户名
            String psw = "G@kusyun040620";      //数据库密码
            con = DriverManager.getConnection(url, usename, psw); //建立连接
            return con;
        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    public static void addStudent(Connection con, Student student) {
//        向表内添加数据
        String sql = "INSERT INTO students (name, age, major, sex, isLeader) VALUES ('" + student.getName() + "', " + student.getAge() + ", '" + student.getMajor() + "', " + student.getSex() + ", " + student.isLeader() + ")";
        try {
            con.createStatement().execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void deleteStudentById(int id) {
        Connection con = mysql.SQLConnect();
        String sql = "DELETE FROM students WHERE id =" + id;
        try {
            con.createStatement().execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static ArrayList<Student> getStudents(Connection con) {
        // 从表内获取数据，并添加到 list 中
        ArrayList<Student> list = new ArrayList<>();
        String sql = "SELECT * FROM students";
        try {
            // 使用 PreparedStatement 避免 SQL 注入
            PreparedStatement pstmt = con.prepareStatement(sql);
            // 执行查询操作
            ResultSet rs = pstmt.executeQuery();
            // 处理结果集
            while (rs.next()) {
                // 假设 Student 类有相应的构造函数或 setter 方法来设置属性
                Student student = new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("age"), rs.getString("major"), rs.getBoolean("sex"), rs.getBoolean("isLeader"));
                list.add(student);
            }
            // 关闭资源
            rs.close();
            pstmt.close();
            return list;
        } catch (SQLException e) {
            // 处理异常
            e.printStackTrace();
        }
        return null;
    }

}